﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.DBM;
using KPIS.GERP.GBMS.DAL;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DALFactory;
using KPIS.GERP.GBMS.IBLL;
using System.Globalization;

namespace KPIS.GERP.GBMS.BLL
{
    public sealed class ProjectControl
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable GetDataProjectExpenseControl(string bgm_year, string job_site_seq, string ctrl_type)
        {
            try
            {
                string strSQL = "";

                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@bgm_year", bgm_year);
                dbManager.AddParameters(1, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(2, "@ctrl_type", ctrl_type);

                strSQL = "select *"
                    + " from(select pe.bgm_source_seq, p.bgm_strat_list_seq, exp.bgm_exp_grp_seq, p.bgm_proj_seq, pe.bgm_proj_exp_seq, pe.bgm_exp_seq"
                    + ", s.bgm_source_name, stra.bgm_strat_list_name, expg.bgm_exp_grp_name, p.bgm_proj_name, c.bgm_proj_exp_ctrl_seq, exp.bgm_exp_name"
                    + ", coalesce(c.amount, 0) + coalesce(c2.amount, 0) as amount"
                    + " from bgm_project_expense_control c"
                    + " left join (select from_bgm_proj_exp_ctrl_seq"
                        + ", sum(coalesce(amount, 0))as amount"
                        + " from bgm_project_expense_control"
                        + " where record_status not like 'D'"
                        + " group by from_bgm_proj_exp_ctrl_seq"
                        + ") c2 on c.bgm_proj_exp_ctrl_seq = c2.from_bgm_proj_exp_ctrl_seq"
                    + " left join bgm_project_expense pe on c.bgm_proj_exp_seq = pe.bgm_proj_exp_seq"
                    + " left join bgm_project p on pe.bgm_proj_seq = p.bgm_proj_seq"
                    + " left join bgm_mas_source s on pe.bgm_source_seq = s.bgm_source_seq"
                    + " left join bgm_mas_expense exp on pe.bgm_exp_seq = exp.bgm_exp_seq"
                    + " left join bgm_mas_expense_group expg on exp.bgm_exp_grp_seq = expg.bgm_exp_grp_seq"
                    + " left join bgm_strategy_data_list stra on p.bgm_strat_list_seq = stra.bgm_strat_list_seq"
                    + " where c.record_status not like 'D'"
                    + " and c.ctrl_type = @ctrl_type"
                    + " and p.bgm_year = @bgm_year"
                    + " and c.from_bgm_proj_exp_ctrl_seq is null"
                    + (ctrl_type != "3" ? " and p.job_site_seq = @job_site_seq" : "")
                    //+ (ctrl_type != "1" ? " and p.bgm_strat_list_seq is null" : "")
                    //+ " group by pe.bgm_source_seq, p.bgm_strat_list_seq, exp.bgm_exp_grp_seq, p.bgm_proj_seq, pe.bgm_proj_exp_seq, pe.bgm_exp_seq"
                    //    + ", s.bgm_source_name, stra.bgm_strat_list_name, expg.bgm_exp_grp_name, p.bgm_proj_name, exp.bgm_exp_name"
                    // + " order by stra.bgm_strat_list_code, s.bgm_source_seq, expg.bgm_exp_grp_seq, p.bgm_proj_seq, pe.bgm_exp_seq"
                    + ") t"
                    + " where t.amount not like 0"
                   
                    //+ " union all"

                    //+ " select pe.bgm_source_seq, p.bgm_strat_list_seq, exp.bgm_exp_grp_seq, p.bgm_proj_seq, pe.bgm_proj_exp_seq, pe.bgm_exp_seq"
                    //+ ", s.bgm_source_name, stra.bgm_strat_list_name, expg.bgm_exp_grp_name, p.bgm_proj_name, c.bgm_proj_exp_ctrl_seq, exp.bgm_exp_name"
                    //+ ", coalesce(c.amount, 0) + coalesce(c2.amount, 0) as amount"
                    //+ " from bgm_project_expense_control c"
                    //+ " left join (select from_bgm_proj_exp_ctrl_seq"
                    //    + ", sum(coalesce(amount, 0))as amount"
                    //    + " from bgm_project_expense_control"
                    //    + " where record_status not like 'D'"
                    //    + " group by from_bgm_proj_exp_ctrl_seq"
                    //    + ") c2 on c.bgm_proj_exp_ctrl_seq = c2.from_bgm_proj_exp_ctrl_seq"
                    //+ " left join bgm_project_expense pe on c.bgm_proj_exp_seq = pe.bgm_proj_exp_seq"
                    //+ " left join bgm_project p on pe.bgm_proj_seq = p.bgm_proj_seq"
                    //+ " left join bgm_mas_source s on pe.bgm_source_seq = s.bgm_source_seq"
                    //+ " left join bgm_mas_expense exp on pe.bgm_exp_seq = exp.bgm_exp_seq"
                    //+ " left join bgm_mas_expense_group expg on exp.bgm_exp_grp_seq = expg.bgm_exp_grp_seq"
                    //+ " left join bgm_strategy_data_list stra on p.bgm_strat_list_seq = stra.bgm_strat_list_seq"
                    //+ " where c.record_status not like 'D'"
                    //+ " and c.ctrl_type = @ctrl_type"
                    //+ " and p.bgm_year = @bgm_year"
                    //+ " and c.from_bgm_proj_exp_ctrl_seq is null"
                    //+ (ctrl_type != "3" ? " and p.job_site_seq = @job_site_seq" : "")
                    ;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Close();
            }
        }

        public DataTable GetDataProjectExpense(string bgm_year, string job_site_seq, string bgm_proj_seq)
        {
            try
            {
                string strSQL = "";

                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@bgm_year", bgm_year);
                dbManager.AddParameters(1, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(2, "@bgm_proj_seq", bgm_proj_seq);

                strSQL = "SELECT pe.bgm_source_seq, p.bgm_strat_list_seq, exp.bgm_exp_grp_seq, p.bgm_proj_seq, pe.bgm_proj_exp_seq"
	                + ", pe.bgm_exp_seq, s.bgm_source_name, stra.bgm_strat_list_name, expg.bgm_exp_grp_name, p.bgm_proj_name, exp.bgm_exp_name"
                    + ", stra.bgm_strat_list_code, rpt.amount, COALESCE (rpt.amount, 0) AS amount"
                    + " FROM bgm_project p"
                    + " LEFT JOIN bgm_project_expense pe ON p.bgm_proj_seq = pe.bgm_proj_seq"
                    + " LEFT JOIN ( SELECT bgm_proj_exp_seq"
		                + ", SUM (COALESCE(cal_amount, 0)) AS amount"
                        + " FROM bgm_project_report WHERE record_status NOT LIKE 'D'"
                        + " GROUP BY bgm_proj_exp_seq"
                        + " ) rpt ON pe.bgm_proj_exp_seq = rpt.bgm_proj_exp_seq"
                    + " LEFT JOIN bgm_mas_source s ON pe.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN bgm_mas_expense exp ON pe.bgm_exp_seq = exp.bgm_exp_seq"
                    + " LEFT JOIN bgm_mas_expense_group expg ON exp.bgm_exp_grp_seq = expg.bgm_exp_grp_seq"
                    + " LEFT JOIN bgm_strategy_data_list stra ON p.bgm_strat_list_seq = stra.bgm_strat_list_seq"
                    + " WHERE p.record_status = 'A'"
                        + " AND p.bgm_year = @bgm_year"
                        + " AND p.job_site_seq = @job_site_seq"
                        + " AND p.bgm_proj_seq = @bgm_proj_seq"
                    + " GROUP BY pe.bgm_source_seq, p.bgm_strat_list_seq, exp.bgm_exp_grp_seq, p.bgm_proj_seq, pe.bgm_proj_exp_seq"
	                + ", pe.bgm_exp_seq, s.bgm_source_name, stra.bgm_strat_list_name, expg.bgm_exp_grp_name, p.bgm_proj_name, exp.bgm_exp_name"
                    + ", stra.bgm_strat_list_code, rpt.amount"
                    + " ORDER BY stra.bgm_strat_list_code, pe.bgm_source_seq, exp.bgm_exp_grp_seq, p.bgm_proj_seq, pe.bgm_exp_seq";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Close();
            }
        }

        public DataTable GetDataDepartmentControl(string bgm_year, string bgm_source_seq, string bgm_output_seq, string bgm_sub_act
            , string bgm_exp_grp_seq, string job_site_seq, bool bIsSubAct)
        {
            try
            {
                string strSelect = "";
                string strJoin = "";
                string strGroupBy = "";

                if (!string.IsNullOrEmpty(bgm_sub_act))
                {
                    strSelect = "select c.bgm_proj_ctrl_seq as seq, grp.bgm_exp_grp_name as name"
                        + ", c.bgm_exp_grp_seq";
                    strJoin = " left join bgm_mas_expense_group grp on c.bgm_exp_grp_seq = grp.bgm_exp_grp_seq";
                    strGroupBy = " group by c.bgm_proj_ctrl_seq, grp.bgm_exp_grp_name";
                }
                else if (!string.IsNullOrEmpty(bgm_output_seq))
                {
                    if (bIsSubAct)
                    {
                        strSelect = "select c.bgm_strat_list_seq as seq, s.bgm_strat_list_name as name";
                        strJoin = " left join bgm_strategy_data_list s on c.bgm_strat_list_seq = s.bgm_strat_list_seq"
                            + " left join bgm_strategy_data_mapping map1 on c.bgm_strat_list_seq = map1.bgm_strat_list_seq"
                                + " and map1.bgm_year = @bgm_year"
                            + " left join bgm_strategy_data_mapping map2 on map1.upper_bgm_strat_data_map_seq = map2.bgm_strat_data_map_seq"
                                + " and map2.bgm_year = @bgm_year"
                            + " left join bgm_strategy_data_mapping map3 on map2.upper_bgm_strat_data_map_seq = map3.bgm_strat_data_map_seq"
                                + " and map3.bgm_year = @bgm_year";
                        strGroupBy = " group by c.bgm_strat_list_seq, s.bgm_strat_list_name";
                    }
                    else
                    {
                        strSelect = "select c.bgm_proj_ctrl_seq as seq, grp.bgm_exp_grp_name as name"
                            + ", c.bgm_exp_grp_seq";
                        strJoin = " left join bgm_mas_expense_group grp on c.bgm_exp_grp_seq = grp.bgm_exp_grp_seq";
                        strGroupBy = " group by c.bgm_proj_ctrl_seq, grp.bgm_exp_grp_name";
                    }
                }
                else if (!string.IsNullOrEmpty(bgm_source_seq))
                {
                    strSelect = "select s.bgm_strat_list_seq as seq, s.bgm_strat_list_name as name";
                    strJoin = " left join bgm_strategy_data_mapping map1 on c.bgm_strat_list_seq = map1.bgm_strat_list_seq"
                            + " and map1.bgm_year = @bgm_year"
                        + " left join bgm_strategy_data_mapping map2 on map1.upper_bgm_strat_data_map_seq = map2.bgm_strat_data_map_seq"
                            + " and map2.bgm_year = @bgm_year"
                        + " left join bgm_strategy_data_mapping map3 on map2.upper_bgm_strat_data_map_seq = map3.bgm_strat_data_map_seq"
                            + " and map3.bgm_year = @bgm_year"
                        + " left join bgm_strategy_data_list s on map3.bgm_strat_list_seq = s.bgm_strat_list_seq";
                    strGroupBy = " group by s.bgm_strat_list_seq, s.bgm_strat_list_name";
                }
                else
                {
                    strSelect = "select c.bgm_source_seq as seq, s.bgm_source_name as name";
                    strJoin = " left join bgm_mas_source s on c.bgm_source_seq = s.bgm_source_seq";
                    strGroupBy = " group by c.bgm_source_seq, s.bgm_source_name";
                }

                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@bgm_year", bgm_year);
                dbManager.AddParameters(1, "@bgm_output_seq", bgm_output_seq);
                dbManager.AddParameters(2, "@bgm_exp_grp_seq", bgm_exp_grp_seq);
                dbManager.AddParameters(3, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(4, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(5, "@sub_act_seq", bgm_sub_act);

                string sql = "select * from ("
                    
                    + strSelect
                    + ", sum(coalesce(ce.amount, 0)) as amount"
                    + " from bgm_project_control c"
                    + " left join bgm_project_control_expense ce on c.bgm_proj_ctrl_seq = ce.bgm_proj_ctrl_seq"
                    + " inner join bgm_strategy_data_list data on c.bgm_strat_list_seq = data.bgm_strat_list_seq"
                        + (bIsSubAct ? " and data.bgm_strat_type_seq = 11" : " and data.bgm_strat_type_seq = 9")
                    + strJoin
                    + " where c.record_status not like 'D'"
                    + " and c.job_site_seq = @job_site_seq"// null = department
                    + (!string.IsNullOrEmpty(bgm_year) ? " and c.bgm_year = @bgm_year" : "")
                    + (!string.IsNullOrEmpty(bgm_sub_act) ? " and c.bgm_strat_list_seq = @sub_act_seq" : "")
                    + (!string.IsNullOrEmpty(bgm_exp_grp_seq) ? " and c.bgm_exp_grp_seq = @bgm_exp_grp_seq" : "")
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " and c.bgm_source_seq = @bgm_source_seq" : "")
                    + (bIsSubAct ? (!string.IsNullOrEmpty(bgm_output_seq) ? " and map3.bgm_strat_list_seq = @bgm_output_seq" : "")
                        : (!string.IsNullOrEmpty(bgm_output_seq) ? " and c.bgm_strat_list_seq = @bgm_output_seq" : ""))
                    + strGroupBy
                    
                    + ") t"
                    + " where t.amount not like 0";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Close();
            }
        }

        public DataTable GetInfo(string bgm_source_seq, string bgm_year, string bgm_strat_list_seq
            , string job_site_seq, string bgm_exp_grp_seq)
        {
            string strSQL = "";

            try
            {
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@bgm_year", bgm_year);
                dbManager.AddParameters(1, "@bgm_strat_list_seq", bgm_strat_list_seq);
                dbManager.AddParameters(2, "@bgm_exp_grp_seq", bgm_exp_grp_seq);
                dbManager.AddParameters(3, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(4, "@job_site_seq", job_site_seq);

                strSQL = "select c.bgm_proj_ctrl_seq, coalesce(ce.amount, 0) as amount"
                    + " from bgm_project_control c"
                    + " left join (select bgm_proj_ctrl_seq"
                        + ", sum(coalesce(amount, 0)) as amount"
                        + " from bgm_project_control_expense"
                        + " group by bgm_proj_ctrl_seq"
                        + ") ce on c.bgm_proj_ctrl_seq = ce.bgm_proj_ctrl_seq"
                    + " where c.record_status not like 'D'"
                    + " and c.job_site_seq = @job_site_seq"// null = department
                    + (!string.IsNullOrEmpty(bgm_year) ? " and c.bgm_year = @bgm_year" : "")
                    + (!string.IsNullOrEmpty(bgm_strat_list_seq) ? " and c.bgm_strat_list_seq = @bgm_strat_list_seq" : "")
                    + (!string.IsNullOrEmpty(bgm_exp_grp_seq) ? " and c.bgm_exp_grp_seq = @bgm_exp_grp_seq" : "")
                    + (!string.IsNullOrEmpty(bgm_source_seq) ? " and c.bgm_source_seq = @bgm_source_seq" : "");
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Close();
            }
        }

        public DataTable GetOutputInfo(string bgm_strat_list_seq, string bgm_year)
        {
            string strSQL = "";

            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@bgm_strat_list_seq", bgm_strat_list_seq);
                dbManager.AddParameters(1, "@bgm_year", bgm_year);

                strSQL = "select data.bgm_strat_list_seq, data.bgm_strat_list_name"
                    + " from bgm_strategy_data_mapping map1"
                    + " left join bgm_strategy_data_mapping map2 on map1.upper_bgm_strat_data_map_seq = map2.bgm_strat_data_map_seq"
                        + " and map2.bgm_year = @bgm_year"
                    + " left join bgm_strategy_data_mapping map3 on map2.upper_bgm_strat_data_map_seq = map3.bgm_strat_data_map_seq"
                        + " and map3.bgm_year = @bgm_year"
                    + " left join bgm_strategy_data_list data on map3.bgm_strat_list_seq = data.bgm_strat_list_seq"
                    + " where map1.bgm_strat_list_seq = @bgm_strat_list_seq"
                    + " and map1.bgm_year = @bgm_year";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Close();
            }
        }

        public DataTable GetProjectExpenseControlSummary(int bgm_year, string ctrl_type)
        {
            try
            {
                string strSQL = "";

                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@bgm_year", bgm_year);
                dbManager.AddParameters(1, "@ctrl_type", ctrl_type);
                dbManager.AddParameters(2, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = @"
select ctrl.bgm_proj_exp_seq, ctrl.amount, ctrl.ctrl_type
, exp.bgm_exp_name, pj.bgm_proj_code, pj.bgm_proj_name
, j.job_site_name, stra.bgm_strat_list_name as sub_act_name
, pje.bgm_source_seq, source.bgm_source_name

from (
select ctrl.bgm_proj_exp_seq, ctrl.ctrl_type, sum(coalesce(ctrl.amount, 0)) as amount
from bgm_project_expense_control ctrl
where ctrl.record_status <> 'D'
and ctrl.ctrl_type = @ctrl_type
group by ctrl.bgm_proj_exp_seq, ctrl.ctrl_type
) ctrl

left join bgm_project_expense pje on ctrl.bgm_proj_exp_seq = pje.bgm_proj_exp_seq
left join bgm_project pj on pje.bgm_proj_seq = pj.bgm_proj_seq
left join bgm_mas_expense exp on pje.bgm_exp_seq = exp.bgm_exp_seq
left join bgm_mas_source source on pje.bgm_source_seq = source.bgm_source_seq
left join bgm_strategy_data_list stra on pj.bgm_strat_list_seq = stra.bgm_strat_list_seq
left join hrm_mp_job_sites j on pj.job_site_seq = j.job_site_seq

where ctrl.amount <> 0
";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Close();
            }
        }
    }
}
